# --coding:utf-8--
import pymysql

"""需求：将设备信息录入到对应数据库表中（`basics_db`.`gw_status_info`，`basics_db`.`equipment_production_info`）"""


class Function(object):
    def __init__(self, db="basics_db"):
        try:
            # 连接数据库
            self.conn = pymysql.Connection(host="rm-bp19h77p0r89dnju94m.mysql.rds.aliyuncs.com",
                                           user="wzy",
                                           password="Wzy159753",
                                           port=3306,
                                           database=db,
                                           charset="utf8"
                                           )
            self.cursor = self.conn.cursor()  # 创建游标
            # self.cursor.execute("SELECT VERSION()")
            # data = self.cursor.fetchone()
            # print(data)
            data = "link ok"
            print(data)
        except:
            data = "not link"
            print(data)

    def mysql_close(self):
        self.cursor.close()  # 关闭游标
        self.conn.close()  # 关闭连接

    # 查看设备网关信息（`basics_db`.`gw_status_info`）
    def gw_look_sql(self, serial_number_id):
        sql = "SELECT * FROM `basics_db`.`gw_status_info` WHERE serial_number_id = '%s'" % serial_number_id
        return sql

    #  将设备信息写入到"basics.db"表下面的"gw_status_info"中
    def gw_status_info_sql(self, serial_number_id, device_type, online_time, status, gw_Location, belong, PK, DN,
                           hard_version, soft_version, gw_context):
        sql = "INSERT INTO `basics_db`.`gw_status_info` (`serial_number_id`, `device_type`,`online_time`,`status`,`gw_Location`,`belong`,`PK`,`DN`,`hard_version`,`soft_version`,`gw_context`) VALUES ('%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ; " % (
            serial_number_id, device_type, online_time, status, gw_Location, belong, PK, DN,
            hard_version, soft_version, gw_context)
        return sql

    #  将设备信息写入"basics_db"表下面的"equipment_production_info"中
    def equipment_sql(self, serial_number_id, device_model, manufacturer, production_batch,
                      production_data,
                      device_type):
        sql = "INSERT INTO `basics_db`.`equipment_production_info`(`serial_number_id`,`device_model`,`manufacturer`,`production_batch`,`production_data`,`device_type`) VALUES ('%s','%s','%s','%s','%s','%s');" % (
            serial_number_id, device_model, manufacturer, production_batch, production_data, device_type)
        return sql

    #  删除设备网关信息（`basics_db`.`gw_status_info`）
    def gw_delete_sql(self, serial_number_id):
        sql = "DELETE FROM `basics_db`.`gw_status_info` WHERE serial_number_id = '%s'" % serial_number_id
        return sql

    def query_data(self, sql):
        print(sql)
        self.cursor.execute(sql)  # 执行sql语句
        try:
            results = self.cursor.fetchall()
            print(results)  # 打印返回结果
        except:
            print("Error: unable to fetch data")
            return "error"
        self.conn.commit()  # 事务提交
        self.mysql_close()
        return results


if __name__ == "__main__":
    gw = Function()
    result = gw.gw_look_sql("cs123")
    gw.query_data(result)
    # result1 = gw.gw_status_info_sql("abc", "-", "-", "-", "-", "-", "-", "-", "-", "-", "-")
    # gw.query_data(result1)
    # result2 = gw.equipment_sql("abc", "-", "-", "-", "-", "-")
    # gw.query_data(result2)
    # result3 = gw.gw_delete_sql("abc")
    # gw.query_data(result3)
